from datetime import datetime
from app import db
from app.utils.time_helper import safe_datetime_format, safe_date_format

class Venue(db.Model):
    """场馆模型"""
    __tablename__ = 'venues'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False, index=True, comment='场馆名称')
    type = db.Column(db.String(50), index=True, comment='场馆类型')
    address = db.Column(db.String(255), comment='场馆地址')
    location = db.Column(db.String(100), index=True, comment='所在区域')
    latitude = db.Column(db.Float, comment='纬度')
    longitude = db.Column(db.Float, comment='经度')
    description = db.Column(db.Text, comment='场馆描述')
    images = db.Column(db.Text, comment='场馆图片，JSON格式存储多张图片URL')
    contact_phone = db.Column(db.String(20), comment='联系电话')
    open_time = db.Column(db.String(100), comment='营业时间描述')
    max_capacity = db.Column(db.Integer, comment='场馆最大容量（人数）')
    status = db.Column(db.SmallInteger, default=1, comment='状态: 0关闭 1开放')
    created_at = db.Column(db.DateTime, default=datetime.now, comment='创建时间')
    updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment='更新时间')
    is_deleted = db.Column(db.Boolean, default=False, comment='是否已删除')
    
    # 关联关系
    time_slots = db.relationship('VenueTimeSlot', backref='venue', lazy='dynamic')
    orders = db.relationship('Order', back_populates='venue', lazy='dynamic')
    
    def __repr__(self):
        return f'<Venue {self.id}: {self.name}>'
    
    def to_dict(self, with_slots=False):
        """转换为字典，用于API响应"""
        import json
        
        # 安全解析images字段
        try:
            images = json.loads(self.images) if self.images else []
        except (json.JSONDecodeError, TypeError):
            # 如果JSON解析失败，返回空列表
            images = []
        
        result = {
            'id': self.id,
            'name': self.name,
            'type': self.type,
            'address': self.address,
            'location': self.location,
            'latitude': self.latitude,
            'longitude': self.longitude,
            'description': self.description,
            'images': images,
            'contact_phone': self.contact_phone,
            'open_time': self.open_time,
            'max_capacity': self.max_capacity,
            'status': self.status,
            'created_at': safe_datetime_format(self.created_at)
        }
        
        if with_slots:
            result['time_slots'] = [slot.to_dict() for slot in self.time_slots.all()]
            
        return result


class VenueTimeSlot(db.Model):
    """场馆时间段模型"""
    __tablename__ = 'venue_time_slots'
    
    id = db.Column(db.Integer, primary_key=True)
    venue_id = db.Column(db.Integer, db.ForeignKey('venues.id'), nullable=False, index=True)
    day_of_week = db.Column(db.SmallInteger, nullable=False, comment='星期几: 1-7代表周一到周日')
    start_time = db.Column(db.String(8), nullable=False, comment='开始时间，格式如09:00')
    end_time = db.Column(db.String(8), nullable=False, comment='结束时间，格式如18:00')
    price = db.Column(db.Numeric(10, 2), nullable=False, comment='场地基础价格（兼容旧版本）')
    per_person_price = db.Column(db.Numeric(10, 2), nullable=False, default=0.00, comment='每人价格')
    min_charge_count = db.Column(db.Integer, default=1, comment='最小收费人数')
    capacity = db.Column(db.Integer, default=1, comment='容量，可同时预约人数')
    status = db.Column(db.SmallInteger, default=1, comment='状态: 0关闭 1开放')
    created_at = db.Column(db.DateTime, default=datetime.now, comment='创建时间')
    updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment='更新时间')
    
    def __repr__(self):
        return f'<VenueTimeSlot {self.id}: {self.venue_id} {self.day_of_week} {self.start_time}-{self.end_time}>'
    
    def to_dict(self):
        """转换为字典，用于API响应"""
        return {
            'id': self.id,
            'venue_id': self.venue_id,
            'day_of_week': self.day_of_week,
            'start_time': self.start_time,
            'end_time': self.end_time,
            'price': float(self.price),
            'per_person_price': float(self.per_person_price),
            'min_charge_count': self.min_charge_count,
            'capacity': self.capacity,
            'status': self.status
        }


class VenueBookingStatus(db.Model):
    """场馆预订状态实时统计模型"""
    __tablename__ = 'venue_booking_status'
    
    id = db.Column(db.Integer, primary_key=True)
    venue_id = db.Column(db.Integer, db.ForeignKey('venues.id'), nullable=False, index=True)
    time_slot_id = db.Column(db.Integer, db.ForeignKey('venue_time_slots.id'), nullable=False, index=True)
    booking_date = db.Column(db.Date, nullable=False, index=True, comment='预订日期')
    booked_count = db.Column(db.Integer, default=0, comment='已预订人数')
    attended_count = db.Column(db.Integer, default=0, comment='实际到场人数')
    capacity = db.Column(db.Integer, nullable=False, comment='容量')
    last_updated = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment='最后更新时间')
    
    # 关联关系
    venue = db.relationship('Venue', backref='booking_status')
    time_slot = db.relationship('VenueTimeSlot', backref='booking_status')
    
    # 复合唯一索引
    __table_args__ = (
        db.Index('idx_venue_date', 'venue_id', 'booking_date'),
        db.Index('idx_timeslot_date', 'time_slot_id', 'booking_date'),
        db.UniqueConstraint('venue_id', 'time_slot_id', 'booking_date', name='uk_venue_timeslot_date'),
    )
    
    def __repr__(self):
        return f'<VenueBookingStatus {self.venue_id}-{self.time_slot_id}-{self.booking_date}>'
    
    def to_dict(self):
        """转换为字典，用于API响应"""
        return {
            'id': self.id,
            'venue_id': self.venue_id,
            'time_slot_id': self.time_slot_id,
            'booking_date': safe_date_format(self.booking_date),
            'booked_count': self.booked_count,
            'attended_count': self.attended_count,
            'capacity': self.capacity,
            'available_spots': self.capacity - self.booked_count,
            'occupancy_rate': round((self.booked_count / self.capacity * 100), 2) if self.capacity > 0 else 0,
            'attendance_rate': round((self.attended_count / self.booked_count * 100), 2) if self.booked_count > 0 else 0,
            'last_updated': safe_datetime_format(self.last_updated)
        }

    @classmethod
    def get_venue_hourly_stats(cls, venue_id, date=None):
        """获取场馆某天的每小时统计数据"""
        from datetime import date as date_type
        if date is None:
            date = date_type.today()
        
        query = cls.query.filter_by(venue_id=venue_id, booking_date=date)
        return query.all()
    
    @classmethod 
    def get_realtime_booking_data(cls, venue_id, time_slot_id, booking_date):
        """获取实时预订数据"""
        return cls.query.filter_by(
            venue_id=venue_id,
            time_slot_id=time_slot_id,
            booking_date=booking_date
        ).first()